|
|
I have a report which creates a
text file for import into a financial system (not
in Access) using the OutputTo
command. The import procedure requires
that each line be no longer than 70
characters. The field being imported is
1000 characters, so I have created a query which
uses the Mid function to load 70 characters
(consecutively) into separate fields.
This
works fine, however the import function in the
financial package does not put the fields back
together as one long string, and some words are
cut in half.
Does anyone have any code
which can split my memo field into separate fields
(to a maximum of 70 characters) but cutting at the
nearest space, rather than in the middle of
words?
Any help would be HUGELY
appreciated!
|
|
|
here's some handy dandy parsing
functions that combined and checking
for the Len(s) should do
it:
-------------------------------------------------
Function
CountWords(S) As Integer
'
' Counts words in
a string separated by 1 or more spaces
'
Dim
WC As Integer, i As Integer, OnASpace As
Integer
If VarType(S) <> 8 Or
Len(Trim(S)) = 0
Then
CountWords =
0
Exit
Function
End If
WC =
0
OnASpace =
True
For i = 1 To
Len(S)
If Mid(S, i, 1)
= " "
Then
OnASpace
=
True
Else
If
OnASpace
Then
OnASpace
=
False
WC
= WC +
1
End
If
End
If
Next i
CountWords
= WC
End
Function
-------------------------------------------
Function
CutWord(S, Remainder)
'
' CutWord: returns
the first word in S.
' Remainder: returns the
rest.
'
Dim Temp, P As
Integer
Temp =
Trim(S)
P = InStr(Temp, "
")
If P = 0 Then P = Len(Temp) +
1
CutWord = Left(Temp, P -
1)
Remainder = Trim(Mid(Temp, P +
1))
End
Function
---------------------------------------
if
you need more help...let me know.
|
|
|
ps. you'll have to
modify both to fit your needs, such as CutWords
should be modified to cutWords before len(s) = 70
then return the rest...
instead of cut the
first word.
|
|
|
Here is a simple function which
accepts two parameters, the text string and the
maximum length of a single line, and returns a
collection of individual lines each less than or
equal to the cut-off length.
Here is how
you should call it:
Dim IndLines As New
Collection
Dim TheString As
String
TheString = "Now is the time for all
good men to come to the aid of their
country"
Set IndLines =
SplitTheString(TheString, 25)
and
here is the function
Private Function
SplitTheString(rStr_InStr As String, rInt_CutOff
As Integer) As
Collection
Dim
lCol_IndLines As
Collection
Dim
lStr_WrkString As
String
Dim
lInt_SpaceLoc As
Integer
Set
lCol_IndLines = New
Collection
lStr_WrkString =
Trim(rStr_InStr)
Do While
(Len(lStr_WrkString) >
rInt_CutOff)
lInt_SpaceLoc
= InStrRev(lStr_WrkString, " ", rInt_CutOff +
1)
If
(lInt_SpaceLoc > 0)
Then
lCol_IndLines.Add
Left(lStr_WrkString, (lInt_SpaceLoc -
1))
lStr_WrkString
= Mid(lStr_WrkString, (lInt_SpaceLoc +
1))
Else
lCol_IndLines.Add
lStr_WrkString
lStr_WrkString
=
vbNullString
End
If
Loop
If
(Len(lStr_WrkString) > 0)
Then
lCol_IndLines.Add
lStr_WrkString
End
If
Set
SplitTheString = lCol_IndLines
End
Function
Good Luck
--------------
As a circle
of light increases so does the circumference of
darkness around it. - Albert
Einstein |
|
|
Thanks very much for the responses
so far - both look like they'll do what I
want. However, I'm pretty new to both
Access and Visual Basic, and I haven't used
Collections before. How do I get the
formatted lines back in to my report? I
have 15 fields, named Expr1 -
Expr15?
Thanks
Helen |
|
|
| |
|
|
 |
|
Activity since last visit
Remove forum(s) from list
Thank Helen267 for this
valuable post!
Click here to mark this thread for e-mail
notification.
